package com.application.model.elegircolegio.database;

import java.sql.SQLException;

import com.application.model.database.ManagerDataBase;
import com.application.model.elegircolegio.entries.ElegirColegioElement;
import com.application.model.elegircolegio.retrieve.RetrieveColegioElegirColegio;
import com.application.model.exceptions.SQLExceptionNoChange;

public class ElegirColegioDB extends ManagerDataBase {

	private RetrieveColegioElegirColegio retrieveColegioElegirColegio;
	public ElegirColegioDB(RetrieveColegioElegirColegio retrieveColegioElegirColegio) {
		super();
		this.retrieveColegioElegirColegio = retrieveColegioElegirColegio;
	}

	public void save(ElegirColegioElement item) {
		
		String nombreColegio = "";
		String pais = "";
		String provincia = "";
		String partido = "";
		String direccion = "";
		String zonaKey = "";
		Double	latitud = 0d;
		Double	longitud = 0d;
		String tipo = "";
		String ideologia = "";
		String urlImagen = "";
		
		String descripcion = "";
		String infraestructura = "";
		String detalleId = "";		
		
		try {
//			if(!item.isInsertInDB()){
				nombreColegio = item.getNombreColegio();
				pais = refactorPais(item.getPais());
				provincia = refactorProvincia(item.getProvincia());
				if(item.getProvincia().matches("Escobar|Los Cardales|Pilar|Del Viso")){
					partido = refactorPartido(item.getProvincia());
				}else{
					partido = refactorPartido(item.getPartido());
				}
				direccion = item.getDireccion();
				zonaKey = item.getZonaKey();
				latitud = item.getLatitud();
				longitud = item.getLongitud();
				tipo = item.getTipo();
				ideologia = item.getIdeologia();
				urlImagen = item.getUrlImagen();
				
				descripcion = item.getDescripcion();
				infraestructura = item.getInfraestructura();
				detalleId = addInstitucionDetalle(nombreColegio, pais, provincia,partido, zonaKey, descripcion, infraestructura);
				addInstitucion(nombreColegio, pais, provincia, partido,direccion, latitud, longitud, tipo, ideologia, urlImagen,detalleId);
				updateLocalidad(nombreColegio, pais, provincia, partido);
				updateBarrio(nombreColegio, pais, provincia, partido);
				
				addJornada(item,nombreColegio, pais, provincia,partido, zonaKey);			
				addNivel(item, nombreColegio, pais, provincia,partido, zonaKey);
				addIdioma(item, nombreColegio, pais, provincia,partido, zonaKey);
				
				item.setInsertInDB(true);
//			}
		} catch (Exception e) {
			retrieveColegioElegirColegio.saveFile();
			logger.error(e);
		}
		
	}

	private String refactorPais(String pais) {
		String value = pais;
		if(value.equalsIgnoreCase("Buenos Aires, Argentina")){
			value="Argentina";
		}
		return value;
	}

	private String refactorProvincia(String provincia) {
		String value = provincia;
		if(provincia.matches("Escobar|Los Cardales|Pilar|Del Viso")){
			value = "G.B.A. Zona Norte";
		}
		return value ;
	}

	private String refactorPartido(String partido) {
		String value = escapeValue(partido);
		if(value.equals("Acassusso")){
			value = "Acassuso";
		}else if(value.equals("Pilar (Barrio Los Robles del Monarca)")){
			value = "Robles Del Monarca";
		}else if(value.equals("Gral. Pacheco")){
			value = "General Pacheco";
		}else if(value.equals("Punta Chica")){
			value = "Pta.Chica";
		}else if(value.equals("Tortugitas")){
			value = "Tortuguitas";
		}else if(value.equals("Villa Pueyrredón")){
			value = "V.Pueyrredon";
		}else if(value.equals("Maquinista Savio")){
			value = "Maq. Savio (Maq. Savio)";
		}else if(value.equals("Lopez Carmelo")){
			value = "Tigre";
		}else if(value.equals("Ing. Maschwitz")){
			value = "Ingeniero Maschwitz";
		}else if(value.equals("Ciudad de Buenos Aires")){
			value = "Capital Federal";
			
		}
		
		
		return value;
	}

	private void updateLocalidad(String nombreColegio, String pais, String provincia, String partido) throws SQLException {
		String query = "";
		try{
			query = "UPDATE institucion SET "+ 
						" localidad_id=IFNULL(localidad_id, "+						
						" ( select id from localidad where UPPER(name) LIKE UPPER('"+getLocalidadName(partido)+"') and partido_id IN (select id from partido where provincia_id IN  "+getSQLProvinciaId(pais,provincia)+" )) "+
						")";
			logger.info(query);
			executeQuery(query);
		}catch (SQLExceptionNoChange e) {
			logger.error(e+"SQL : "+query,e.fillInStackTrace());
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
		
	}

	private void updateBarrio(String nombreColegio, String pais, String provincia, String partido) throws SQLException {
		String query = "";
		try{
			query = "UPDATE institucion SET "+ 
						" barrio_id="+
						" ( select id from barrio where " +
						" UPPER(name) LIKE UPPER('"+getNombreBarrio(partido)+"') and " +
						" localidad_id IN ( SELECT id from localidad where " +
												"partido_id IN (select id from partido where provincia_id IN  "+getSQLProvinciaId(pais,provincia)+" )) "+
						" ) ";
			logger.info(query);
			executeQuery(query);
		}catch (SQLExceptionNoChange e) {
			logger.error(e+"SQL : "+query,e.fillInStackTrace());
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
		
	}
	private String getNombreBarrio(String barrio) {
		String value = "";
		if(barrio.equals("Florida Este")){
			barrio = "Florida%/Este";
		}else if(barrio.equals("Florida Oeste")){
			barrio = "Florida%/Oeste";
		}else if(barrio.equals("Florida Oste")){
			barrio = "Florida%/Oeste";
		}
		value  = escapeValue(barrio);
		return value;
	}

	private String addInstitucionDetalle(String nombreColegio, String pais,String provincia, String partido, String zonaKey,
			String descripcion, String infraestructura) throws SQLException {
		String query = "";
		String id = "NULL";
		int 	transaction =0;
		try{
			query = "INSERT INTO detalle(infraestructura,descripcion) "
			        + "VALUES("
			        + "'"+escapeValue(infraestructura)+"',"                    
			        + "'"+escapeValue(descripcion)+"'"
//			        + " "+getSQLInstitucionId(nombreColegio, pais, provincia, partido, zonaKey)
			        + ");";
			logger.info(query);
			id = executeQuery(query);
//			if (transaction>0) {
//				id = getLastInsertId();	
//			}			
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
		return id;
	}

	

	private void addIdioma(ElegirColegioElement item,String nombreColegio, String pais, String provincia,String partido, String zonaKey) throws SQLException {
		String query = "";
		try{
			for (String idioma : item.getIdiomas()) {
				query = "INSERT INTO idioma_institucion(idioma_id,institucion_id) "
			                    + "VALUES("
			                    + " (SELECT id from idioma WHERE UPPER(name) LIKE UPPER('%"+getSQLIdioma(idioma)+"%')),"
			                    + " "+getSQLInstitucionId(nombreColegio, pais, provincia, partido, zonaKey)
			                    + ")";
				logger.info(query);
				executeQuery(query);
			}
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
	}

	private String getSQLIdioma(String idioma) {
		String value = "";
		value = escapeValue(idioma);
		if(value.contains("Ingl")){
			value = value.substring(0, 4);
		}else if(value.contains("Franc")){
			value = value.substring(0, 5);
		}else if(value.contains("Alem")){
			value = value.substring(0, 4);			
		}else{
			
		}
		return value;
	}

	private void addNivel(ElegirColegioElement item,String nombreColegio, String pais, String provincia,String partido, String zonaKey) throws SQLException {		
		String query = "";
		try{	
			for (String nivel : item.getNiveles()) {
				query="INSERT INTO nivel_institucion(nivel_id,institucion_id) "
			        + " VALUES("
			        + " (SELECT id FROM nivel WHERE UPPER(name) LIKE UPPER('%"+getNivelSQL(nivel)+"%')),"
			        + " "+getSQLInstitucionId(nombreColegio, pais, provincia, partido, zonaKey)
			        + ")";
				logger.info(query);
				executeQuery(query);
			}
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
	}

	private String getNivelSQL(String escapeValue) {
		String value = "";
		value = escapeValue(escapeValue);
		if(escapeValue.contains("Infantes")){
			value = escapeValue.substring(11, escapeValue.length());
		}
		return value;
	}

	private void addJornada(ElegirColegioElement item,String nombreColegio, String pais, String provincia,String partido, String zonaKey) throws SQLException {
		String query = "";
		try{
			for (String jornada : item.getJornadas()) {
				query="INSERT INTO jornada_institucion(jornada_id,institucion_id) "
			            + "VALUES("                        
			            + " (SELECT id from jornada WHERE UPPER(name)=UPPER('"+escapeValue(jornada)+"')),"
			            + " "+getSQLInstitucionId(nombreColegio, pais, provincia, partido, zonaKey)
			            + ")";
				logger.info(query);
				executeQuery(query);
			}
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
	}

	private void addInstitucion(String nombreColegio, String pais,String provincia, String partido, String direccion, Double latitud,
			Double longitud, String tipo, String ideologia, String urlImagen, String detalleId)throws SQLException {
		String query = "";
		try{
			query = "INSERT INTO institucion (name,latitud,longitud,direccion,urlfotologo," +
					"pais_id,provincia_id,partido_id,tipo_id,ideologia_id,detalle_id) " +
					" VALUES ('"+escapeValue(nombreColegio)+"',"+
							" " +latitud+","+longitud+",'" +escapeValue(direccion)+"','"+escapeValue(urlImagen)+"',"+
							" "+getSQLPaisId(pais)+","+
							" "+getSQLProvinciaId(pais,provincia,partido)+"," +
							" "+getSQLPartidoId(pais, provincia,partido)+","+							
							"(SELECT id FROM ideologia where UPPER(name)=UPPER('"+escapeValue(ideologia)+"')),"+
							"(SELECT id FROM tipo where UPPER(name) LIKE UPPER('%"+getSQLTipo(escapeValue(tipo))+"%'))," +
							""+detalleId+
							")";
			logger.info(query);
			executeQuery(query);
		}catch (Exception e) {
			logger.error(query);
			throw new SQLException(e);
		}
	}
	

	private String getSQLTipo(String escapeValue) {
		String value = "";
		if(!escapeValue.isEmpty()){
			value = escapeValue.substring(0,escapeValue.length()-1);
			value = escapeValue.substring(3,escapeValue.length()-1);
		}
		return value;
	}

	private String getSQLInstitucionId(String nombreColegio,String pais,String provincia,String partido,String zonaKey){
		String sql = "";
		sql ="(SELECT id FROM institucion WHERE "+getWhereColegioUpdate(pais, provincia, partido, nombreColegio,zonaKey)+")";
		return sql;
	}
	
	private String getSQLProvinciaId(String pais,String provincia,String partido){
		String sql = "";
		if(provincia.equals("Buenos Aires")){
			String partidoSQL = "";
			String localidad = "";
			String barrio = "";

			partidoSQL = " (SELECT provincia_id FROM partido where UPPER(name) LIKE UPPER('"+getPartidoName(partido)+"') AND provincia_id IN "+getSQLProvinciaId(pais,provincia)+")";
			localidad = " (SELECT provincia_id FROM partido where id = (select partido_id from localidad where UPPER(name) LIKE UPPER('"+getLocalidadName(partido)+"') and partido_id IN (select id from partido where provincia_id IN ( "+getSQLProvinciaId(pais,provincia)+" ) )) ) ";
			barrio = "(SELECT provincia_id FROM partido where id = ( "+getSQLPartidoIdFromLocalidadAndBarrio(pais, provincia, partido)+" ) )";
			sql =" IFNULL(" +partidoSQL+",IFNULL("+localidad+","+barrio+") )";
		}else{
			sql ="(SELECT id FROM provincia where UPPER(name)=UPPER('"+escapeValue(provincia)+"') AND pais_id = "+getSQLPaisId(pais)+" )";
		}
		return sql;
	}
	private String getPartidoName(String partido) {
		String value = escapeValue(partido);
		value = getLocalidadName(value);
		return value;
	}

	private String getLocalidadName(String localidad) {
		String value = escapeValue(localidad);
		if(localidad.equalsIgnoreCase("Villa Urquiza")){
			value = "V%Urquiza";
		}else if(localidad.equalsIgnoreCase("Rincón")){
			value = "%Rinc%";
		}
		return value;
	}

	protected String getSQLProvinciaId(String pais,String provincia){
		String sql = "";
		if(provincia.equals("Buenos Aires")){
			//,'Interior Buenos Aires'
			sql ="(SELECT id FROM provincia where name IN ('G.B.A. Zona Sur','G.B.A. Zona Norte','G.B.A. Zona Oeste','Capital Federal') AND pais_id = "+getSQLPaisId(pais)+" )";
		}else{
			sql ="(SELECT id FROM provincia where UPPER(name)=UPPER('"+escapeValue(provincia)+"') AND pais_id = "+getSQLPaisId(pais)+" )";
		}
		return sql;
	}
	protected String getSQLPartidoId(String pais,String provincia,String partido){
		String sql = "";
		String partidoSQL = "";
		String localidad = "";
		String barrio = "";
		partidoSQL =" (SELECT id FROM partido where UPPER(name)=UPPER('"+getPartidoName(partido)+"') AND provincia_id IN "+getSQLProvinciaId(pais,provincia)+")";
		localidad = " (SELECT partido_id from localidad where UPPER(name) LIKE UPPER('"+getLocalidadName(partido)+"') and partido_id IN (select id from partido where provincia_id IN ( "+getSQLProvinciaId(pais,provincia)+"  )))";
		barrio = getSQLPartidoIdFromLocalidadAndBarrio(pais, provincia, partido);
		sql = " IFNULL( "+partidoSQL+" , IFNULL("+localidad+","+barrio+" ) ) ";
		return sql;
	}
	public String getSQLPartidoIdFromLocalidadAndBarrio(String pais,String provincia,String partido){
		String partidoId = "";
		partidoId = " (SELECT partido_id from localidad where id IN (select localidad_id from barrio where UPPER(name) LIKE UPPER('"+getNombreBarrio(partido)+"') ) and partido_id IN (select id from partido where provincia_id IN ( "+getSQLProvinciaId(pais,provincia)+"  )))";
		return partidoId;
	}
	protected String getSQLLocalidadId(String pais,String provincia,String partido,String localidad){
		String sql = "";
		sql ="(SELECT id FROM localidad where UPPER(name) LIKE UPPER('"+getLocalidadName(localidad)+"') AND partido_id = "+getSQLPartidoId(pais,provincia,partido)+")";
		return sql;
	}
	
	protected String getWhereColegioUpdate(String pais,String provincia,String partido,String nameColegio, String zonaKey){
		String where ="";
		if(zonaKey.equals("Capital Federal")){
			
			where = " name='"+escapeValue(nameColegio)+"' AND " +
			" pais_id= "+getSQLPaisId(pais)+" AND " +
			" provincia_id= "+getSQLProvinciaId(pais, provincia,partido)+" AND " +
			" partido_id= "+getSQLPartidoId(pais,zonaKey,zonaKey);
			if(!partido.equalsIgnoreCase("Capital Federal")){
			where += " AND" +
			" localidad_id = "+getSQLLocalidadId(pais,zonaKey,zonaKey,partido);
			}
		}else if(provincia.equals("Buenos Aires")){
			where = " name='"+escapeValue(nameColegio)+"' AND " +
					" pais_id= "+getSQLPaisId(pais)+" AND " +
					" provincia_id= "+getSQLProvinciaId(pais, provincia, partido)+" AND " +
					" partido_id= "+getSQLPartidoId(pais,provincia,partido);
		}else{
			where = " name='"+escapeValue(nameColegio)+"' AND " +
				" pais_id= "+getSQLPaisId(pais)+" AND " +
				" provincia_id= "+getSQLProvinciaId(pais, provincia)+" AND " +
				" partido_id= "+getSQLPartidoId(pais,provincia,partido);
		}
		return where;
	}
	
}
